/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Sharif
 */
public class DBCostumer implements IFDBCustomer {
     private  Connection con;
    /** Creates a new instance 
     * of DBCustomer */
    public DBCostumer() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all customers
    public ArrayList getAllCostumers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one customer having the id
    public Costumer findCostumer(String id, boolean retriveAssociation)
    {   String wClause = " Customer.id=" + id;
        return singleWhere(wClause, retriveAssociation);
    }
    //find one customer having the name
    public Costumer searchCostumerName(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchCostumer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
     
     //insert a new customer
    public int insertCostumer(Costumer cus)
    {  
        int nextSSN = GetMax.getMaxId("Select max(id) from Customer");
        nextSSN = nextSSN + 1;
        System.out.println("next ssn = " +  nextSSN);
  
       int rc = -1;
	   String query="INSERT INTO Customer(id, name, address,cityZipid,phoneno)  VALUES("+
	  		nextSSN + ",'" +
                        cus.getName()  + "','"  +
		    	cus.getAddress()  + "',"  +
                        "1" + ", ' " + cus.getPhoneno() + "')";
                     
           
      System.out.println("insert : " + query);		  					
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in costumer db: "+ex);
	   }
	   return(rc);
	}
	
	public int updateCostumer(Costumer emp)
	{
		Costumer empObj  = emp;
		int rc=-1;
	  
		String query="UPDATE Customer SET "+
		 	  "name ='"+ empObj.getName()+"', "+
		 	  "address ='"+ empObj.getAddress() + "', " +
                          "cityZipId = " + empObj.getCityZipId() + "," +
                          "phoneno='" + emp.getPhoneno() + "'" +
		          " WHERE id = "+ empObj.getId();
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in costumer db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(int id)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Customer WHERE id =" +
				id;
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in costumer db: "+ex);
   	        }
		return(rc);
	}
	
	
	
	 
	private ArrayList miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
//			int id=0;
			while( results.next() ){
		     	 Costumer empObj = new Costumer();
			 empObj = buildCostumer(results);
                         list.add(empObj);	
			}//end while
                        // if retrieveAssociation is tru, the supervisor and department
                        // has to be selected for each object in the list
			stmt.close();
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	
	private Costumer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Costumer empObj = new Costumer();
//        empObj.setId(0);
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            empObj = buildCostumer(results);
                            //assocaition is to be build
                            stmt.close();


			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	//method to build the query - Mathias: Added join statement
	private String buildQuery(String wClause)
	{
	    String query="Select Customer.id, name,address,phoneno,pCode,city,cityZipId from Customer, CityZip " +
                    "Where Customer.cityZipId=CityZip.id";
            
		if (wClause.length()>0)
			query=query+" AND "+ wClause;
			
		return query;
	}
	//method to build an customer object
	private Costumer buildCostumer(ResultSet results)
      {   Costumer empObj = new Costumer();
          try{
                empObj.setId(results.getInt(1));
                empObj.setName(results.getString(2));
                empObj.setAddress(results.getString(3));
                empObj.setPhoneno(results.getString(4));
                empObj.setZipCode(results.getString(5));
                empObj.setCity(results.getString(6));
                empObj.setCityZipId(results.getInt(7));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return empObj;
      }

}
